﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.Linq;
using DataObject.Database;
using DataObject.Core;
using DataObject.Helpers;
using DataObject.Model;

namespace DataObject.DataAccess
{
    public class RouteDAO : DataBaseProcessBase<Route, OBSDataContext>
    {
        #region Singleton
        public static RouteDAO CreateInstant()
        {
            return new RouteDAO();
        }
        #endregion

        public bool ChangeState(Guid routeID)
        {
            bool result = false;
            try
            {
                using (OBSDataContext db = new OBSDataContext(base.connectionString))
                {
                    var route = db.Routes.SingleOrDefault(x => x.RouteID == routeID);
                    if (route != null)
                    {
                        route.Active = !route.Active;

                        db.SubmitChanges();

                        result = true;
                    }
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

        public IList<Route> GetAllRoutes()
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return biocode.Routes.Where(x => x.Active).OrderBy(x => x.RouteNumber).ThenBy(x => x.SubRouteCode).ToList();
            }
        }

        public IList<Route> GetRoutes(string routeNumber, string routeName,
            int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false; //disabling will turn off deferred loading (lazy fetch)

                var query = (from x in db.Routes select x);
                if (!String.IsNullOrEmpty(routeNumber))
                {
                    string[] routeArray = routeNumber.Split('-');
                    int route = -1;
                    if(int.TryParse(routeArray[0], out route))
                    {
                        query = query.Where(x => x.RouteNumber == route);
                    }
                    char sub;
                    if (routeArray.Length > 1 && char.TryParse(routeArray[1], out sub))
                    {
                        query = query.Where(x => x.SubRouteCode == sub);
                    }
                }
                if (!String.IsNullOrEmpty(routeName))
                    query = query.Where(x => x.RouteName.ToLower().Contains(routeName.ToLower()));
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }

        public RouteDTO GetRoute(int routeNumber, char? subRouteCode)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return RouteMapper.ToBusinessObject(biocode.Routes.SingleOrDefault(a => a.RouteNumber == routeNumber && a.SubRouteCode == subRouteCode));
            }
        }

        public RouteDTO GetRoute(System.Guid routeID)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return RouteMapper.ToBusinessObjectHasChildren(biocode.Routes.SingleOrDefault(a => a.RouteID == routeID), typeof(RouteService));
            }
        }

        public bool InsertRoute(RouteDTO model)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = RouteMapper.ToEntity(new Route(), model);
                // Prepare data
                foreach (var routeService in model.RouteServiceCollection)
                {
                    routeService.RouteServiceID = Helper.NewSeqGuid();
                    routeService.RouteID = model.RouteID;
                    entity.RouteServices.Add(RouteServiceMapper.ToEntity(new RouteService(), routeService));
                }

                if (model.SubRouteCode.HasValue && model.SubRouteCode.Value != ' ' &&
                    biocode.ParameterEntities.Count(x => x.GroupName == "SubRoute" &&
                        x.ShortValue[0] == Char.ToUpper(model.SubRouteCode.Value)) == 0)
                {
                    // Insert parameters
                    entity.SubRouteCode = Char.ToUpper(model.SubRouteCode.Value);

                    var maxID = 1;
                    if (biocode.ParameterEntities.Count() > 0)
                    {
                        maxID = biocode.ParameterEntities.Max(x => x.ParamID);
                        maxID++;
                    }
                    biocode.ParameterEntities.InsertOnSubmit(new ParameterEntity
                    {
                        ParamID = maxID,
                        GroupName = "SubRoute",
                        Active = true,
                        AllowedModify = false,
                        ShortValue = entity.SubRouteCode.ToString()
                    });
                }

                biocode.Routes.InsertOnSubmit(entity);

                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
                catch
                {

                }
            }

            return result;
        }

        public bool UpdateRoute(RouteDTO model)
        {
            bool result = false;

            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = biocode.Routes.Where(x => x.RouteID == model.RouteID).SingleOrDefault();
                if (entity != null)
                {
                    biocode.RouteServices.DeleteAllOnSubmit(entity.RouteServices);

                    model.Active = true;
                    RouteMapper.ToEntity(entity, model);
                    foreach (var routeService in model.RouteServiceCollection)
                    {
                        routeService.RouteServiceID = Helper.NewSeqGuid();
                        routeService.RouteID = model.RouteID;
                        entity.RouteServices.Add(RouteServiceMapper.ToEntity(new RouteService(), routeService));
                    }

                    if (model.SubRouteCode.HasValue && model.SubRouteCode.Value != ' ' && biocode.ParameterEntities.Count(x => x.GroupName == "SubRoute" && x.ShortValue[0] == Char.ToUpper(model.SubRouteCode.Value)) == 0)
                    {
                        entity.SubRouteCode = Char.ToUpper(model.SubRouteCode.Value);

                        var maxID = 1;
                        if (biocode.ParameterEntities.Count() > 0)
                        {
                            maxID = biocode.ParameterEntities.Max(x => x.ParamID);
                            maxID++;
                        }
                        biocode.ParameterEntities.InsertOnSubmit(new ParameterEntity
                        {
                            ParamID = maxID,
                            GroupName = "SubRoute",
                            Active = true,
                            AllowedModify = false,
                            ShortValue = entity.SubRouteCode.ToString()
                        });
                    }

                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  A possible concurrency exception occurred.  Let's see if
                        //  we can resolve it.
                        foreach (var conflict in biocode.ChangeConflicts)
                        {
                            conflict.Resolve(RefreshMode.KeepCurrentValues);
                        }
                        try
                        {
                            biocode.SubmitChanges();
                            result = true;
                        }
                        catch (ChangeConflictException)
                        {
                            //  It didn't work, so throw a new exception.
                            // throw new Exception("A concurrency error occurred!");
                        }
                        catch (Exception)
                        {
                            // throw new Exception("There was an error saving this record!");
                        }
                    }
                    catch
                    {
                        // twice
                        try
                        {
                            biocode.SubmitChanges();
                            result = true;
                        }
                        catch (Exception)
                        {
                            // throw new Exception("There was an error saving this record!");
                        }
                    }
                }
            }

            return result;
        }

        public bool DeleteRoute(Guid routeID)
        {
            bool result = false;
            try
            {
                using (OBSDataContext db = new OBSDataContext(base.connectionString))
                {
                    var route = db.Routes.SingleOrDefault(x => x.RouteID == routeID);
                    if (route != null)
                    {
                        // Check this route exists in reservation.
                        if (db.Reservations.Count(x => x.RouteNumber == route.RouteNumber && x.SubRouteCode == route.SubRouteCode) > 0)
                        {
                            route.Active = false;
                        }
                        else
                        {
                            // Don't need deactive
                            if (route.RouteServices.Count > 0)
                                db.RouteServices.DeleteAllOnSubmit(route.RouteServices);

                            db.Routes.DeleteOnSubmit(route);
                        }

                        db.SubmitChanges();

                        result = true;
                    }
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

    }
}
